# Copyright (c) 2025, hzmes and contributors
# For license information, please see license.txt


import frappe
from frappe import _

def execute(filters=None):
	# get_data要传参filters
	return get_columns(filters), get_data(filters)

def get_columns(filters):
	columns = [
		{"label": _("Expense"),"fieldtype": "Data","fieldname": "expense","width": 238,},
	]
	sql_project = """ 
				SELECT distinct detail_project_number as project FROM `tabGL Voucher Allocation` tb1 JOIN `tabProject` tb2 on tb1.detail_project_number = tb2.`name`
				UNION ALL 
				SELECT distinct project FROM `tabExpense Allocation` tb1 JOIN `tabProject` tb2 on tb1.project = tb2.`name`
				UNION ALL 
				SELECT distinct project FROM `tabStock Entry` tb1 JOIN `tabProject` tb2 on tb1.project = tb2.`name`
	"""
	# 计算有哪些项目明细动态增加列
	projects = frappe.db.sql(sql_project, as_dict=True)
	if len(projects) > 0:
		for item in projects:
			element = {"label": item.project,"fieldtype": "Data","fieldname": item.project,"width": 200,}
			columns.append(element)
	return columns

def get_data(filters):
	# 获取filters
	from_date, to_date = filters.get('from_date'), filters.get('to_date')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	total = []	# 默认空列表
	sql_total = f""" 
		SELECT detail_project_number as project, detail_expense_name as expense, ROUND(SUM(amount),2) as amount FROM `tabGL Voucher Allocation` GROUP BY detail_project_number, detail_expense_name
		UNION ALL
		SELECT project, expense_claim_type as expense, ROUND(SUM(amount),2) as amount FROM `tabExpense Allocation` GROUP BY project, expense_claim_type
		UNION ALL
		SELECT project, '物料消耗' as expense, ROUND(SUM(total_outgoing_value),2) as amount FROM `tabStock Entry` WHERE stock_entry_type = 'Material Issue' GROUP BY project
	 """
	# 计算所有项目的费用发生额 Group By
	total = frappe.db.sql(sql_total, as_dict=True)
	if len(total) > 0:
		sql_expense = """ SELECT DISTINCT tb1.expense FROM
					(
					SELECT DISTINCT detail_expense_name as expense FROM `tabGL Voucher Allocation`
					UNION ALL
					SELECT DISTINCT expense_claim_type as expense FROM `tabExpense Allocation` 
					UNION ALL
					SELECT '物料消耗' as expense
					) tb1
		"""
		# 计算有哪些费用明细
		expenses = frappe.db.sql(sql_expense, as_dict=True)
		if len(expenses) > 0:
			# 往expenses元素中增加鍵值对: {"项目": amount}
			for i in expenses:
				for pro in total:
					if pro.expense == i.expense:
						i[pro.project] = pro.amount
	return expenses